/****** Object:  Stored Procedure dbo.asi_PurgeInactiveCampaigns    Script Date: 7/3/2003 4:58:05 PM ******/
CREATE PROCEDURE [dbo].[asi_PurgeInactiveCampaigns]

AS

declare @campaignKey uniqueidentifier
declare @appealKey uniqueidentifier
declare @solicitationKey uniqueidentifier
declare @sourceCodeKey uniqueidentifier
declare @campaignCode nvarchar(10)

set ROWCOUNT 1

--Select an inactive Campaign for deletion
SELECT @campaignKey = CampaignKey, @campaignCode = LegacyCampaignCode FROM vBoCampaign WHERE CampaignStatusCode = 1

while @@ROWCOUNT > 0
begin
    set ROWCOUNT 0
    --Start a transaction
    begin tran

    --Select an Appeal for deletion
    SELECT @appealKey = AppealKey FROM vBoAppeal WHERE CampaignKey = @campaignKey
    while @@ROWCOUNT > 0
    begin
        --Select a Solicitation for deletion
        SELECT @solicitationKey = SolicitationKey FROM vBoSolicitation WHERE AppealKey = @appealKey
        while @@ROWCOUNT > 0
        begin
            --Select a SourceCode for deletion
            SELECT @sourceCodeKey = SourceCodeKey FROM vBoSourceCode WHERE SolicitationKey = @solicitationKey
            while @@ROWCOUNT > 0
            begin
                --Delete related rows
                DELETE FROM ListItem WHERE ListKey = @sourceCodeKey
                DELETE FROM ListMain WHERE ListKey = @sourceCodeKey
                DELETE FROM SolicitationSource WHERE SourceCodeKey = @sourceCodeKey

                --Delete the Source Code
                DELETE FROM SourceCode WHERE SourceCodeKey = @sourceCodeKey
                DELETE FROM UniformRegistry WHERE UniformKey = @sourceCodeKey
                --Select the next Source Code for deletion
                SELECT @sourceCodeKey = SourceCodeKey FROM vBoSourceCode WHERE SolicitationKey = @solicitationKey
            end
            
            --Delete the Solicitation
            DELETE FROM SolicitationMain WHERE SolicitationKey = @solicitationKey
            DELETE FROM UniformRegistry WHERE UniformKey = @solicitationKey
            --Select the next Solicitation for deletion
            SELECT @solicitationKey = SolicitationKey FROM vBoSolicitation WHERE AppealKey = @appealKey
        end

        --Delete the Appeal
        DELETE FROM AppealMain WHERE AppealKey = @appealKey
        DELETE FROM UniformRegistry WHERE UniformKey = @appealKey
        --Select the next Appeal for deletion
        SELECT @appealKey = AppealKey FROM vBoAppeal WHERE CampaignKey = @campaignKey    
    end

    --Delete the Campaign
    DELETE FROM CampaignMain WHERE CampaignKey = @campaignKey
    DELETE FROM UniformRegistry WHERE UniformKey = @campaignKey
    DELETE FROM Campaign WHERE CAMPAIGN_CODE = @campaignCode
    
    --Finalize the transaction
    commit tran

    --Select the next inactive Campaign for deletion
    set ROWCOUNT 1
    SELECT @campaignKey = CampaignKey, @campaignCode = LegacyCampaignCode FROM vBoCampaign WHERE CampaignStatusCode = 1
end

GO